package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.*;

import cn.edu.sdjzu.xg.bysj.service.*;
import lombok.extern.slf4j.Slf4j;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;
import util.StringHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.TreeSet;

@Slf4j
public final class GraduateProjectDao {
    private static GraduateProjectDao graduateProjectDao = null;

    public static GraduateProjectDao getInstance() {
        if (graduateProjectDao == null) {
            graduateProjectDao = new GraduateProjectDao();
        }
        return graduateProjectDao;
    }

    public static Collection<GraduateProject> findAll(List conditionList,
                                                      Pagination pagination,
                                                      Connection connection) throws SQLException {
        //创建集合类对象，用来保存所有记录代表的GraduateProject对象
        Collection<GraduateProject> graduateProjects = new TreeSet<GraduateProject>();
        //准备预编译的语句
        int totalNum = CommonDao.getInstance().count(connection, "GraduateProject");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM GraduateProject ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditionList != null) {
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if (pagination != null) {
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }
        //执行预编译语句，结果集放在resultSet中
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt.executeQuery();
        //遍历resultSet
        while (resultSet.next()) {
            graduateProjects.add(new GraduateProject(resultSet.getInt("id"),
                    resultSet.getString("title"),
                    resultSet.getString("score"),
                    resultSet.getInt("year"),
                    GraduateProjectCategoryService.getInstance().find(resultSet.getInt("graduateProjectCategory_id")),
                    GraduateProjectTypeService.getInstance().find(resultSet.getInt("graduateProjectType_id")),
                    GraduateProjectStatusService.getInstance().find(resultSet.getInt("graduateProjectStatus_id")),
                    GraduateProjectSourceService.getInstance().find(resultSet.getInt("graduateProjectSource_id")),
                    GraduateProjectTruenessService.getInstance().find(resultSet.getInt("graduateProjectTrueness_id")),
                    TeacherService.getInstance().find(resultSet.getInt("teacher_id")),
                    StudentService.getInstance().find(resultSet.getInt("student_id"))
            ));
        }
        JdbcHelper.close(resultSet,pstmt);
        return graduateProjects;
    }

    public static GraduateProject find(int id, Connection connection) throws SQLException {

        //创建sql语句
        String find_sql = "select * from graduateProject where id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //创建结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        GraduateProject graduateProject = null;
        //游标下移
        if (resultSet.next()) {
            //创建GraduateProject对象，各字段为结果集中相应字段
            graduateProject = new GraduateProject(resultSet.getInt("id"),
                    resultSet.getString("title"),
                    resultSet.getString("score"),
                    resultSet.getInt("year"),
                    GraduateProjectCategoryService.getInstance().find(resultSet.getInt("graduateProjectCategory_id")),
                    GraduateProjectTypeService.getInstance().find(resultSet.getInt("graduateProjectType_id")),
                    GraduateProjectStatusService.getInstance().find(resultSet.getInt("graduateProjectStatus_id")),
                    GraduateProjectSourceService.getInstance().find(resultSet.getInt("graduateProjectSource_id")),
                    GraduateProjectTruenessService.getInstance().find(resultSet.getInt("graduateProjectTrueness_id")),
                    TeacherService.getInstance().find(resultSet.getInt("teacher_id")),
                    StudentService.getInstance().find(resultSet.getInt("student_id"))
            );
        }
        JdbcHelper.close(resultSet,pstmt);
        return graduateProject;
    }

    public boolean update(GraduateProject graduateProject, Connection connection) throws SQLException {
        //创建sql语句
        String updateGPC_sql = "update GraduateProject set"
                + " title=?,graduateProjectCategory_id=?,graduateProjectType_id=?,"
                + "graduateProjectStatus_id=?,graduateProjectSource_id=?,graduateProjectTrueness_id=?"
                + " where id=?";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateGPC_sql);
        //为预编译参数赋值
        pstmt.setString(1, graduateProject.getTitle());
        pstmt.setInt(2, graduateProject.getGraduateProjectCategory().getId());
        pstmt.setInt(3, graduateProject.getGraduateProjectType().getId());
        pstmt.setInt(4, 1);
        pstmt.setInt(5, graduateProject.getGraduateProjectSource().getId());
        pstmt.setInt(6, graduateProject.getGraduateProjectTrueness().getId());
        pstmt.setInt(7, graduateProject.getId());
        //根据执行结果，具体判断所要返回的值
        if (pstmt.executeUpdate() == 0) {
            JdbcHelper.close(null, pstmt);
            return false;
        } else
            JdbcHelper.close(null, pstmt);
        return true;
    }

    public boolean add(GraduateProject graduateProject, Connection connection)
            throws SQLException {
        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空格
        //创建sql语句
        String addGPC_sql =
                "insert into GraduateProject (title,graduateProjectCategory_id,graduateProjectType_id, "
                        + "graduateProjectStatus_id,graduateProjectSource_id,graduateProjectTrueness_id,teacher_id"
                        + ") values "
                        + "(?,?,?,?,?,?,?);";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addGPC_sql);
        //为预编译参数赋值
        pstmt.setString(1, graduateProject.getTitle());
        pstmt.setInt(2, graduateProject.getGraduateProjectCategory().getId());
        pstmt.setInt(3, graduateProject.getGraduateProjectType().getId());
        pstmt.setInt(4, 1);
        pstmt.setInt(5, graduateProject.getGraduateProjectSource().getId());
        pstmt.setInt(6, graduateProject.getGraduateProjectTrueness().getId());
        pstmt.setInt(7, graduateProject.getTeacher().getId());
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //根据结果，具体判断所要返回的值
        if (affectedRowNum == 1) {
            JdbcHelper.close(null, pstmt);
            return true;
        } else
            JdbcHelper.close(null, pstmt);
        return false;
    }
    //暂存的修改
    public boolean updateScratch(GraduateProject graduateProject, Connection connection) throws SQLException {
        //创建sql语句
        String updateGPC_sql = "update GraduateProject set"
                + " title=?,graduateProjectCategory_id=?,graduateProjectType_id=?,"
                + "graduateProjectStatus_id=?,graduateProjectSource_id=?,graduateProjectTrueness_id=?"
                + " where id=?";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateGPC_sql);
        //为预编译参数赋值
        pstmt.setString(1, graduateProject.getTitle());
        pstmt.setInt(2, graduateProject.getGraduateProjectCategory().getId());
        pstmt.setInt(3, graduateProject.getGraduateProjectType().getId());
        pstmt.setInt(4, 2);
        pstmt.setInt(5, graduateProject.getGraduateProjectSource().getId());
        pstmt.setInt(6, graduateProject.getGraduateProjectTrueness().getId());
        pstmt.setInt(7, graduateProject.getId());
        //根据执行结果，具体判断所要返回的值
        if (pstmt.executeUpdate() == 0) {
            JdbcHelper.close(null, pstmt);
            return false;
        } else
            JdbcHelper.close(null, pstmt);
        return true;
    }
    //暂存的添加方法
    public boolean addScratch(GraduateProject graduateProject, Connection connection) throws SQLException {
        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空格
        //创建sql语句
        String addGPC_sql =
                "insert into GraduateProject (title,graduateProjectCategory_id,graduateProjectType_id, "
                        + "graduateProjectStatus_id,graduateProjectSource_id,graduateProjectTrueness_id,teacher_id"
                        + ") values "
                        + "(?,?,?,?,?,?,?);";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addGPC_sql);
        //为预编译参数赋值
        pstmt.setString(1, graduateProject.getTitle());
        pstmt.setInt(2, graduateProject.getGraduateProjectCategory().getId());
        pstmt.setInt(3, graduateProject.getGraduateProjectType().getId());
        pstmt.setInt(4, 2);
        pstmt.setInt(5, graduateProject.getGraduateProjectSource().getId());
        pstmt.setInt(6, graduateProject.getGraduateProjectTrueness().getId());
        pstmt.setInt(7, graduateProject.getTeacher().getId());
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //根据结果，具体判断所要返回的值
        if (affectedRowNum == 1) {
            JdbcHelper.close(null, pstmt);
            return true;
        } else
            JdbcHelper.close(null, pstmt);
        return false;
    }



    public static boolean delete(int id, Connection connection) throws SQLException {

        //创建sql语句
        String deleteGraduateProject_sql = "DELETE FROM GraduateProject WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteGraduateProject_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        if (pstmt.executeLargeUpdate() == 1) {
            JdbcHelper.close(null, pstmt);
            return true;
        } else {
            JdbcHelper.close(null, pstmt);
            return false;
        }
    }

    public static boolean delete(List<GraduateProject> List, Connection connection) throws SQLException {

        //创建sql语句
        StringBuilder deleteGraduateProject_sql = new StringBuilder("DELETE FROM GraduateProject WHERE id=");
        for (GraduateProject graduateProject:List){
            deleteGraduateProject_sql.append(graduateProject.getId()+" or ");
        }
        StringHelper.truncEnding(deleteGraduateProject_sql, 4);
        String sql_string = deleteGraduateProject_sql.toString();
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(sql_string);

        if (pstmt.executeLargeUpdate() == 1) {
            JdbcHelper.close(null, pstmt);
            return true;
        } else {
            JdbcHelper.close(null, pstmt);
            return false;
        }
    }

    public Collection<GraduateProject> findAll(Connection connection) throws SQLException {
        Collection<GraduateProject> desiredGraduateProjects = new HashSet<GraduateProject>();
        StringBuilder sql_select = new StringBuilder("SELECT*FROM GraduateProject");
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt_select.executeQuery();
        while (resultSet.next()) {

            //根据id查找结果创建GraduateProject对象
            GraduateProject graduateProject = new GraduateProject(resultSet.getInt("id"),
                    resultSet.getString("title"),
                    resultSet.getString("score"),
                    resultSet.getInt("year"),
                    GraduateProjectCategoryService.getInstance().find(resultSet.getInt("graduateProjectCategory_id")),
                    GraduateProjectTypeService.getInstance().find(resultSet.getInt("graduateProjectType_id")),
                    GraduateProjectStatusService.getInstance().find(resultSet.getInt("graduateProjectStatus_id")),
                    GraduateProjectSourceService.getInstance().find(resultSet.getInt("graduateProjectSource_id")),
                    GraduateProjectTruenessService.getInstance().find(resultSet.getInt("graduateProjectTrueness_id")),
                    TeacherService.getInstance().find(resultSet.getInt("teacher_id")),
                    StudentService.getInstance().find(resultSet.getInt("student_id")));
            desiredGraduateProjects.add(graduateProject);
        }
        JdbcHelper.close(resultSet, pstmt_select);
        log.debug("" + desiredGraduateProjects);
        return desiredGraduateProjects;
    }

}


